package dbutils.dao.impl;


import dbutils.dao.StaffDao;
import dbutils.entity.Staff;
import dbutils.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * StaffDao接口的实现 - 基于原生 JDBC 实现
 *
 * @author ZQX
 * @date 2025-02-25
 */
public class StaffDaoImpl4Jdbc implements StaffDao {
    @Override
    public int insert(Staff staff) {
        int row = 0 ;

        // 第一：定义要操作数据库的 SQL 语句
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?)" ;

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();


        PreparedStatement ps = null ;

        try {
            // 第三：预编译SQL语句
            ps = conn.prepareStatement(sql);

            // 第四：填充参数
            ps.setString(1,staff.getName());
            ps.setInt(2,staff.getAge());
            ps.setString(3,staff.getPhone());
            ps.setString(4,staff.getStaPos());

            // 第五：执行SQL语句
            row = ps.executeUpdate();

            // 第六：对执行结果进行处理
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 第七：关闭对象
            DbUtil.close(null,ps,conn);
        }

        return row;
    }

    @Override
    public int delete(int id) {
        String sql = "delete from staffs where id=?";
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            return pst.executeUpdate();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Staff staff) {
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=? where id=?";
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,staff.getName());
            pst.setInt(2,staff.getAge());
            pst.setString(3,staff.getPhone());
            pst.setString(4,staff.getStaPos());
            pst.setInt(5,staff.getId());

            return pst.executeUpdate();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Staff> selectByPage(int currentPage, int pageSize) {
        List<Staff> list = new ArrayList<>();

        // 第一：定义要操作数据库的 SQL 语句
        String sql = "select * from staffs limit ?,?" ;

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();


        PreparedStatement ps = null ;
        ResultSet rs = null ;

        try {
            // 第三：预编译SQL语句
            ps = conn.prepareStatement(sql);

            // 第四：填充参数
            int limit = (currentPage-1) * pageSize ;
            ps.setInt(1,limit);
            ps.setInt(2,pageSize);

            // 第五：执行SQL语句
            rs = ps.executeQuery();

            // 第六：对执行结果进行处理
            while(rs.next()) {
                // 1.读取各列数据
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String phone = rs.getString(4);
                String staPos = rs.getString(5);

                // 把 java.sql.Timestamp 类型转换为 java.time.LocalDateTime
                LocalDateTime addTime = rs.getTimestamp(6)
                        .toLocalDateTime();

                LocalDateTime updateTime = rs.getTimestamp(7)
                        .toLocalDateTime();

                // 2.实例化实体对象
                Staff staff = new Staff();
                staff.setId(id);
                staff.setName(name);
                staff.setAge(age);
                staff.setPhone(phone);
                staff.setStaPos(staPos);
                staff.setAddTime(addTime);
                staff.setUpdateTime(updateTime);

                // 3.把实体对象添加到 List 集合中
                list.add(staff) ;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 第七：关闭对象
            DbUtil.close(rs,ps,conn);
        }

        return list;
    }

    @Override
    public Staff selectById(int id) {
        String sql = "select * from staffs where id=?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            rs = pst.executeQuery();
            while(rs.next()){
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String phone = rs.getString(4);
                String staPos = rs.getString(5);
                LocalDateTime addTime = rs.getTimestamp(6)
                        .toLocalDateTime();
                LocalDateTime updateTime = rs.getTimestamp(7)
                        .toLocalDateTime();
                Staff staff = new Staff();
                staff.setId(id);
                staff.setName(name);
                staff.setAge(age);
                staff.setPhone(phone);
                staff.setStaPos(staPos);
                staff.setAddTime(addTime);
                staff.setUpdateTime(updateTime);
                return staff;
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 第七：关闭对象
            DbUtil.close(rs,pst,conn);
        }
        return null;
    }

}
